﻿CREATE PROCEDURE [dbo].[proc_Finance_RunningAccount_Non_GetList]
	(
		@CompanyId int,
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@PaymentTypeId int,
		@BankId int,
		@Sort varchar(100),
		@Stext nvarchar(50),
		@AccountsTypeId int,
		@StartIndex int,
		@EndIndex int,
		@checkxj bit,
		@rblbank INT,
		@Agent VARCHAR(20)
	
	)
AS
BEGIN
	
	DECLARE @TempStr VARCHAR(500),@StrSql NVARCHAR(4000)
SET @TempStr=''

SET @TempStr=@TempStr+' R.CompanyId='+CONVERT(VARCHAR, @CompanyId)+' and '

if @AccountsTypeId<>-1
begin
	SET @TempStr=@TempStr+' R.AccountsTypeId='+ CONVERT(VARCHAR, @AccountsTypeId)+' and '
end



IF @PaymentTypeId=1 --现金
BEGIN   --查现金时，还需要查询电汇中需要算在现金中的电汇账号
	--SET @TempStr='(R.PaymentTypeId=1 
	--                  or r.BankId IN (SELECT id FROM Finance_Bank fb WHERE fb.PaymentTypeId=1 and Companyid='+CONVERT(VARCHAR, @CompanyId)+')) and '
    IF @checkxj=0
    BEGIN
    	SET @TempStr=@TempStr+'R.PaymentTypeId=1  and '
    END
    ELSE 
    	BEGIN
    		SET @TempStr=@TempStr+'(R.PaymentTypeId=1 
	                  or r.BankId IN (SELECT id FROM Finance_Bank fb WHERE fb.PaymentTypeId=1 and Companyid='+CONVERT(VARCHAR, @CompanyId)+')) and '
    	END
END
ELSE IF @PaymentTypeId=2 --银行
BEGIN
	IF  @BankId=0 --电汇账号选择为全部时，查询所有算在银行中的电汇金额
	BEGIN
		SET @TempStr=@TempStr+'R.PaymentTypeId=2  and r.BankId IN (SELECT id FROM Finance_Bank fb WHERE fb.PaymentTypeId='+CONVERT(VARCHAR, @rblbank)+' and Companyid='+CONVERT(VARCHAR, @CompanyId)+') and '
	END
	ELSE
		BEGIN--选择了电汇账号时，查询该账号下的金额
			SET @TempStr=@TempStr+'R.PaymentTypeId=2  and r.BankId = (select id from Finance_Bank where id='+CONVERT(VARCHAR, @BankId)+' and  PaymentTypeId='+CONVERT(VARCHAR, @rblbank)+') and '
		END
END
ELSE IF @PaymentTypeId=3 --支票
BEGIN
	SET @TempStr=@TempStr+'(R.PaymentTypeId=3 ) and '
END
ELSE
	BEGIN
		set @TempStr=@TempStr+' R.PaymentTypeId in(1,2,3)  and '
	END
IF @Agent<>''
BEGIN
	SET @TempStr=@TempStr+' UserName='''+@Agent+''' and  '
END

if @Stext <>''
begin 
	SET @TempStr=@TempStr+' R.Summary like ''%'+ @Stext +'%'' and '
end

If @sort =''
Begin
	Set @sort='R.CreateDate Desc'
End	
	
	SET @StrSql='
	Declare @sRq Datetime,@eRq Datetime,@eRq_s Datetime
	Set @sRq = Convert(Datetime, '''+@StartDate + ' 00:00:00'')
	Set @eRq = Convert(Datetime, '''+@EndDate + ' 23:59:59'')
	;WITH list As(					

Select ROW_NUMBER() OVER (ORDER BY '+@sort+' )AS Row,
		R.Id,
		R.RId,
		R.CreateDate,
		R.TradeDate,
		R.Summary,
		R.AccountsTypeId,
		R.AccountsTypeName,	
		R.PaymentTypeId,
		R.PaymentTypeName,
		R.BankId,
		R.BankName,
		R.Income,		
		R.Agent,
		R.AgentName,
		R.UserName,
		R.EName,
		R.BillNumber,		
		R.CompanyId,
		 CompanyName

	From Finance_RunningAccount_Non R 
	Where 	 
	 '+@TempStr+'  R.TradeDate between @sRq and @eRq
	   	 )

	Select *, (Select Count(1) From list
	) as DataCount 
	,(select sum(Income) from list) as DataSum From list Where Row between '+  CONVERT(VARCHAR, @StartIndex) +' and '+ CONVERT(VARCHAR, @EndIndex) +'	   
	   '
	   PRINT(@StrSql)
	   EXEC(@StrSql)
	   
end
	RETURN
